﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using CMP.ERP.Model;
using CMP.ERP.Helper;
using System.Configuration;
using System.Data.SqlClient;

namespace CMP.ERP.DAL
{
    public class StockDAL
    {
        /// <summary>
        /// 实例化对象
        /// </summary>
        SQLHelper p = new SQLHelper();

        string connSql = ConfigurationManager.ConnectionStrings["ConnMySql"].ToString();
        /// <summary>
        /// 获取库存信息
        /// </summary>
        /// <returns></returns>
        public List<Stock> GetStockAll(int PageSize, int PageIndex)
        {
            string sql = "select  top (" + PageSize + ") * from (select ROW_NUMBER() over(order by Stock_Id) as rowid ,s.Stock_Id,c.Commodity_Id,w.WareHose_Id,c.Commodity_Name,w.WareHose_Name,Stock_Num,s.Reserve_Num from Stock s join Commodity c on s.Commodity_Id=c.Commodity_Id inner join WareHose w on s.WareHose_Id=w.WareHose_Id where 1=1)as A where rowid> (" + PageSize + ")*(" + PageIndex + "-1)";
            SqlDataAdapter adapter = new SqlDataAdapter(sql, connSql);
            DataTable table = new DataTable();
            adapter.Fill(table);
            List<Stock> list = new List<Stock>();
            foreach (DataRow item in table.Rows)
            {
                Stock s = new Stock();
                s.Commodity_Id = Convert.ToInt32(item["Commodity_Id"]);
                s.WareHose_Id = Convert.ToInt32(item["WareHose_Id"]);
                s.Stock_Id = Convert.ToInt32(item["Stock_Id"]);
                s.Commodity_Name = item["Commodity_Name"].ToString();
                s.WareHose_Name = item["WareHose_Name"].ToString();
                s.Stock_Num = Convert.ToInt32(item["Stock_Num"]);
                s.Reserve_Num = Convert.ToInt32(item["Reserve_Num"]);
                list.Add(s);
            }
            return list;
        }
        public int GetStockCount()
        {
            string Procname = "Proc_StockCount";
            return Convert.ToInt32(p.ExecScalar_Proc(Procname));

        }
        /// <summary>
        /// 根据id获取库存信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Stock GetStockById(int id)
        {
            string sql = "select s.Stock_Id,c.Commodity_Id,w.WareHose_Id,c.Commodity_Name,w.WareHose_Name,Stock_Num from Stock s join Commodity c on s.Commodity_Id=c.Commodity_Id inner join WareHose w on s.WareHose_Id=w.WareHose_Id where Stock_Id=" + id + "";
            SqlDataAdapter adapter = new SqlDataAdapter(sql, connSql);
            DataTable table = new DataTable();
            adapter.Fill(table);
            Stock s = new Stock();
            foreach (DataRow item in table.Rows)
            {
                s.Commodity_Id = Convert.ToInt32(item["Commodity_Id"]);
                s.WareHose_Id = Convert.ToInt32(item["WareHose_Id"]);
                s.Stock_Id = Convert.ToInt32(item["Stock_Id"]);
                s.Commodity_Name = item["Commodity_Name"].ToString();
                s.WareHose_Name = item["WareHose_Name"].ToString();
                s.Stock_Num = Convert.ToInt32(item["Stock_Num"]);
            }
            return s;
        }
        /// <summary>
        /// 获取商品名称下拉框
        /// </summary>
        /// <returns></returns>
        public List<Commodity> GetCnameAll()
        {
            string sql = "select Commodity_Id,Commodity_Name from Commodity";
            SqlDataAdapter adapter = new SqlDataAdapter(sql, connSql);
            DataTable table = new DataTable();
            adapter.Fill(table);
            List<Commodity> list = new List<Commodity>();
            foreach (DataRow item in table.Rows)
            {
                Commodity c = new Commodity();
                c.Commodity_Id = Convert.ToInt32(item["Commodity_Id"]);
                c.Commodity_Name = (item["Commodity_Name"]).ToString();
                list.Add(c);
            }
            return list;
        }
        /// <summary>
        /// 获取仓库名称下拉框
        /// </summary>
        /// <returns></returns>
        public List<WareHose> GetWnameAll()
        {
            string sql = "select WareHose_Id,WareHose_Name from WareHose";
            SqlDataAdapter adapter = new SqlDataAdapter(sql, connSql);
            DataTable table = new DataTable();
            adapter.Fill(table);
            List<WareHose> list = new List<WareHose>();
            foreach (DataRow item in table.Rows)
            {
                WareHose w = new WareHose();
                w.WareHose_Id = Convert.ToInt32(item["WareHose_Id"]);
                w.WareHose_Name = (item["WareHose_Name"]).ToString();
                list.Add(w);
            }
            return list;
        }
        /// <summary>
        /// 更新库存
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        public int UpdStock(Stock s)
        {
            string sql = "update Stock set Commodity_Id=" + s.Commodity_Id + ",WareHose_Id=" + s.WareHose_Id + ",Stock_Num=" + s.Stock_Num + " where Stock_Id=" + s.Stock_Id + "";
            return p.ExecNonquery(sql);
        }
        /// <summary>
        /// 添加库存
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        public int AddStock(Stock s)
        {
            string sql = "insert into Stock values("+s.Commodity_Id+","+s.WareHose_Id+","+s.Stock_Num+")";
            return p.ExecNonquery(sql);
        }
        /// <summary>
        /// 根据商品名称查询商品信息
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public List<Stock> sousuo(string name,int? WareHose_Id,int PageSize,int PageIndex)
        {
            string sql = string.Format("select  top (" + PageSize + ") * from (select ROW_NUMBER() over(order by Stock_Id) as rowid ,s.Stock_Id,c.Commodity_Id,w.WareHose_Id,c.Commodity_Name,w.WareHose_Name,Stock_Num,s.Reserve_Num from Stock s join Commodity c on s.Commodity_Id=c.Commodity_Id inner join WareHose w on s.WareHose_Id=w.WareHose_Id where 1=1 ");
            if (WareHose_Id>0)
            {
                sql += "and c.Commodity_Name like '%" + name + "%' and w.WareHose_Id like '%" + WareHose_Id + "%'";
            }
            else
            {
                sql += "and c.Commodity_Name like '%" + name + "%'";
            }
            sql += ")as A where rowid> (" + PageSize + ")*(" + PageIndex + "-1)";
            SqlDataAdapter adapter = new SqlDataAdapter(sql, connSql);
            DataTable table = new DataTable();
            adapter.Fill(table);
            List<Stock> list = new List<Stock>();
            foreach (DataRow item in table.Rows)
            {
                Stock s = new Stock();
                s.Commodity_Id = Convert.ToInt32(item["Commodity_Id"]);
                s.WareHose_Id = Convert.ToInt32(item["WareHose_Id"]);
                s.Stock_Id = Convert.ToInt32(item["Stock_Id"]);
                s.Commodity_Name = item["Commodity_Name"].ToString();
                s.WareHose_Name = item["WareHose_Name"].ToString();
                s.Stock_Num = Convert.ToInt32(item["Stock_Num"]);
                list.Add(s);
            }
            return list;
        }
        /// <summary>
        ///获取库存数量小于预警数量的个数
        /// </summary>
        /// <returns></returns>
        public int Countearly()
        {
            string str = "select COUNT(*) from Stock where Stock_Num<Stock_Early";
            return Convert.ToInt32(p.ExecScalar(str));
        }
        /// <summary>
        /// 预警库存显示
        /// </summary>
        /// <returns></returns>
        public DataTable Jingjie()
        {
            string str="select Stock_Id,Commodity_Name,WareHose_Name,Stock_Num from Stock s join Commodity c on s.Commodity_Id=c.Commodity_Id join WareHose w on s.WareHose_Id=w.WareHose_Id where Stock_Num<Stock_Early";
            return p.ExecDataTable(str);
        }
    }
}
